﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using dotnetOP.Base;
using dotnetOP.Attributes;

namespace dotnetOP.Data {
    /// <summary>
    /// 
    /// </summary>
    public class ObjectAdapter {
        private string prefix = "dnop";
        private string connectionString;

        public string Prefix {
            get {
                return prefix;
            }
            set {
                prefix = value;
            }
        }

        public ObjectAdapter(string connectionString) {
            this.connectionString = connectionString;
        }

        public void SaveObject(DnopObject o) {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            try {
                conn = new SqlConnection(connectionString);
                conn.Open();

                cmd = new SqlCommand(string.Format("{0}_Save{1}", prefix, o.GetType().Name), conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", o.ID);
                foreach (FieldInfo f in o.GetType().GetFields()) {
                    if (!f.FieldType.IsArray) {
                        object value = f.GetValue(o);
                        if (value == null) {
                            value = DBNull.Value;
                        }
                        if (f.FieldType.Equals(typeof(string)) || f.FieldType.Equals(typeof(int)) || f.FieldType.Equals(typeof(long)) ||
                            f.FieldType.Equals(typeof(float)) || f.FieldType.Equals(typeof(double)) || f.FieldType.Equals(typeof(bool)) ||
                            f.FieldType.Equals(typeof(DateTime))) {
                            cmd.Parameters.AddWithValue(string.Format("@{0}", f.Name), value);
                        } else if (f.FieldType.IsClass) {
                            DnopObject obj = (DnopObject)f.GetValue(o);
                            SaveObject(obj);
                            cmd.Parameters.AddWithValue(string.Format("@{0}_ID", f.Name), obj.ID);
                        }
                    }
                }
                o.ID = Convert.ToInt32(cmd.ExecuteScalar());

                foreach (FieldInfo f in o.GetType().GetFields()) {
                    if (f.FieldType.IsArray) {
                        object val = f.GetValue(o);
                        if (val != null) {
                            DnopObject[] objs = (DnopObject[])val;
                            foreach (DnopObject obj in objs) {
                                SaveObject(obj);
                                cmd = new SqlCommand(string.Format("{0}_SaveRef{1}_{2}", prefix, o.GetType().Name, f.Name), conn);
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue(string.Format("@{0}_ID", o.GetType().Name), o.ID);
                                cmd.Parameters.AddWithValue(string.Format("@{0}_ID", obj.GetType().Name), obj.ID);
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }

                conn.Close();
            } finally {
                if (conn.State != ConnectionState.Closed) {
                    conn.Close();
                }
            }
        }

        public void DeleteObject(DnopObject o) {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            try {
                conn = new SqlConnection(connectionString);
                conn.Open();

                foreach (FieldInfo f in o.GetType().GetFields()) {
                    if (f.FieldType.IsArray) {
                        DnopObject[] objs = f.GetValue(o) as DnopObject[];
                        if (objs != null) {
                            foreach (DnopObject obj in objs) {
                                DeleteObject(obj);
                                cmd = new SqlCommand(string.Format("{0}_DeleteRef{1}_{2}", prefix, o.GetType().Name, f.Name), conn);
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue(string.Format("@{0}_ID", o.GetType().Name), o.ID);
                                cmd.Parameters.AddWithValue(string.Format("@{0}_ID", obj.GetType().Name), obj.ID);
                                cmd.ExecuteNonQuery();
                            }
                        }
                    } else if (f.FieldType.IsClass) { 
                        DnopObject obj = f.GetValue(o) as DnopObject;
                        if (obj != null) {
                            DeleteObject(obj);
                        }
                    }
                }

                cmd = new SqlCommand(string.Format("{0}_Delete{1}", prefix, o.GetType().Name), conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", o.ID);
                cmd.ExecuteNonQuery();

                conn.Close();
            } finally {
                if (conn.State != ConnectionState.Closed) {
                    conn.Close();
                }
            }
        }

        public DnopObject GetObject(int id, Type type) {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            try {
                conn = new SqlConnection(connectionString);
                conn.Open();

                cmd = new SqlCommand(string.Format("{0}_Get{1}ByID", prefix, type.Name), conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ID", id);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable tbl = new DataTable();
                da.Fill(tbl);

                DataRow row = tbl.Rows[0];
                DnopObject obj = (DnopObject)type.InvokeMember(type.Name, BindingFlags.CreateInstance, null, null, null);
                obj.ID = (int)row["ID"];
                foreach (FieldInfo f in type.GetFields()) {
                    if (f.FieldType.BaseType.Equals(typeof(DnopObject))) {
                        DnopObject o = GetObject((int)row[f.Name + "_ID"], f.FieldType);
                        f.SetValue(obj, o);
                    } else if (f.FieldType.IsArray) {
                        cmd = new SqlCommand(string.Format("{0}_GetRef{1}_{2}", prefix, type.Name, f.Name), conn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue(string.Format("@{0}_ID", type.Name), obj.ID);
                        da = new SqlDataAdapter(cmd);
                        DataTable refTbl = new DataTable();
                        da.Fill(refTbl);

                        DnopObject[] o = (DnopObject[])Array.CreateInstance(f.FieldType.GetElementType(), refTbl.Rows.Count);
                        for (int i = 0; i < o.Length; i++) {
                            o[i] = GetObject((int)refTbl.Rows[i][string.Format("{0}_ID", f.FieldType.GetElementType().Name)], f.FieldType.GetElementType());
                        }
                        f.SetValue(obj, o);
                    } else {
                        if (row[f.Name] != DBNull.Value) {
                            f.SetValue(obj, row[f.Name]);
                        }
                    }
                }

                return obj;
            } finally {
                if (conn.State != ConnectionState.Closed) {
                    conn.Close();
                }
            }
        }

        public DnopObject[] GetObject(Dictionary<string, object> searchCriteria, Type type) {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                cmd = new SqlCommand(string.Format("{0}_Search{1}", prefix, type.Name), conn);
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (string key in searchCriteria.Keys)
                {
                    cmd.Parameters.AddWithValue(string.Format("@{0}", key), searchCriteria[key]);
                }
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable tbl = new DataTable();
                da.Fill(tbl);

                DnopObject[] res = new DnopObject[tbl.Rows.Count];
                for (int ii = 0; ii < tbl.Rows.Count; ii++)
                {
                    DataRow row = tbl.Rows[ii];
                    DnopObject obj = (DnopObject)type.InvokeMember(type.Name, BindingFlags.CreateInstance, null, null, null);
                    obj.ID = (int)row["ID"];
                    foreach (FieldInfo f in type.GetFields())
                    {
                        if (f.FieldType.BaseType.Equals(typeof(DnopObject)))
                        {
                            DnopObject o = GetObject((int)row[f.Name + "_ID"], f.FieldType);
                            f.SetValue(obj, o);
                        }
                        else if (f.FieldType.IsArray)
                        {
                            cmd = new SqlCommand(string.Format("{0}_GetRef{1}_{2}", prefix, type.Name, f.Name), conn);
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.Parameters.AddWithValue(string.Format("@{0}_ID", type.Name), obj.ID);
                            da = new SqlDataAdapter(cmd);
                            DataTable refTbl = new DataTable();
                            da.Fill(refTbl);

                            DnopObject[] o = (DnopObject[])Array.CreateInstance(f.FieldType.GetElementType(), refTbl.Rows.Count);
                            for (int i = 0; i < o.Length; i++)
                            {
                                o[i] = GetObject((int)refTbl.Rows[i][string.Format("{0}_ID", f.FieldType.GetElementType().Name)], f.FieldType.GetElementType());
                            }
                            f.SetValue(obj, o);
                        }
                        else
                        {
                            if (row[f.Name] != DBNull.Value)
                            {
                                f.SetValue(obj, row[f.Name]);
                            }
                        }
                    }

                    res[ii] = obj;
                }
                return res;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
        }
    }
}
